package org.ne.framework.data.sql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.ne.framework.data.core.JdbcPersistence;
import org.ne.framework.data.page.Page;
import org.ne.framework.data.util.ChkTools;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.orm.hibernate4.HibernateCallback;
import org.springframework.orm.hibernate4.HibernateTemplate;
import org.springframework.stereotype.Service;

@Service
public class JdbcComponent implements JdbcPersistence {

	@Autowired
	protected JdbcTemplate jt;
	@Autowired
	public HibernateTemplate ht;

	/**
	 * 如果第一个结果是 ResultSet 对象，则返回 true；如果第一个结果是更新计数或者没有结果，则返回 false
	 */

	/**
	 * 分页查询
	 */
	@Override
	public List<Map<String, Object>> queryForList(String sql, Page page) {
		return queryForList(sql, null, page);
	}

	@Override
	public List<Map<String, Object>> queryForList(String sql, Object[] vals,
			Page page) {
		Integer position = sql.toLowerCase().indexOf("from ");
		String sql_count = "select count(*) cnt " + sql.substring(position);

		return this.queryForList(sql, sql_count, vals, page);
	}

	public List<Map<String, Object>> queryForList(String sql, String sql_count,
			Object[] vals, Page page) {

		List<Map<String, Object>> list = jt.queryForList(sql_count, vals);

		long count = 0l;
		if (list.size() > 0) {
			Number num = (Number) list.get(0).get("cnt");
			count = num.longValue();
		}

		page.setRowTotal(count);

		return onlyQueryForList(sql, vals, page);
	}

	private List<Map<String, Object>> onlyQueryForList(final String sql,
			final Object[] vals, final Page page) {

		List<Map<String, Object>> list = ht
				.execute(new HibernateCallback<List<Map<String, Object>>>() {
					@Override
					public List<Map<String, Object>> doInHibernate(
							Session session) throws HibernateException {
						SQLQuery query = session.createSQLQuery(sql);
						query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

						// 设置值
						if (ChkTools.isNotNull(vals)) {
							for (int i = 0; i < vals.length; i++) {
								query.setParameter(i, vals[i]);
							}
						}

						query.setFirstResult(page.getNewBeginResult());
						query.setMaxResults(page.getPageSize());
						List<Map<String, Object>> list = query.list();
						return list;
					}
				});

		return list;
	}

	/**
	 * 万能分页查询
	 */
	@Override
	public List<Map<String, Object>> queryForListUniversal(final String sql,
			final Object[] vals, Page page) {

		long count = jt.execute(sql, new PreparedStatementCallback<Long>() {

			@Override
			public Long doInPreparedStatement(PreparedStatement pstmt)
					throws SQLException, DataAccessException {
				if (ChkTools.isNotNull(vals)) {

					for (int i = 0; i < vals.length; i++) {
						pstmt.setObject(i + 1, vals[i]);
					}
				}
				ResultSet rs = pstmt.executeQuery();
				rs.last();
				long count = rs.getRow();
				return count;
			}
		});

		page.setRowTotal(count);

		String sql_new = sql;
		return onlyQueryForList(sql_new, vals, page);

	}

}
